<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>SQL Editor with Autocomplete</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/codemirror/5.65.5/codemirror.min.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/codemirror/5.65.5/theme/material.min.css">
    <style type="text/css">
    	body, html {
    	    margin: 0;
    	    padding: 0;
    	    width: 100%;
    	    height: 100%;
    	    display: flex;
    	    justify-content: center;
    	    align-items: center;
    	    background-color: #f0f0f0;
    	}
    	
    	#editor-container {
    	    width: 80%;
    	    height: 80%;
    	    display: flex;
    	    flex-direction: column;
    	    background-color: white;
    	    border: 1px solid #ccc;
    	    box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
    	}
    	
    	#editor {
    	    flex: 1;
    	    border-bottom: 1px solid #ccc;
    	}
    	
    	#run-sql {
    	    padding: 10px;
    	    background-color: #007bff;
    	    color: white;
    	    border: none;
    	    cursor: pointer;
    	    text-align: center;
    	}
    	
    	#results {
    	    flex: 1;
    	    overflow-y: auto;
    	    padding: 10px;
    	}

    </style>
</head>
<body>
    <div id="editor-container">
        <textarea id="editor"></textarea>
        <button id="run-sql">Run SQL</button>
        <div id="results"></div>
    </div>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/codemirror/5.65.5/codemirror.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/codemirror/5.65.5/mode/sql/sql.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/codemirror/5.65.5/addon/hint/show-hint.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/codemirror/5.65.5/addon/hint/sql-hint.min.js"></script>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/codemirror/5.65.5/addon/hint/show-hint.min.css">
    <script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.6.1/sql-wasm.js"></script>
    <script type="text/javascript">
    	let editor;
    	let db;
    	
    	document.addEventListener('DOMContentLoaded', async () => {
    	    // Initialize CodeMirror Editor
    	    editor = CodeMirror.fromTextArea(document.getElementById('editor'), {
    	        mode: 'text/x-sql',
    	        theme: 'material',
    	        lineNumbers: true,
    	        extraKeys: {"Ctrl-Space": "autocomplete"},
    	        hintOptions: {tables: {
    	            users: ["name", "score", "birthDate"],
    	            countries: ["name", "population", "size"]
    	        }}
    	    });
    	
    	    editor.setValue("SELECT * FROM test;");
    	
    	    // Load SQL.js and initialize the database
    	    const SQL = await initSqlJs({ locateFile: file => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.6.1/sql-wasm.wasm` });
    	    db = new SQL.Database();
    	
    	    // Preload a sample table
    	    db.run("CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT);");
    	    db.run("INSERT INTO test (name) VALUES ('Alice'), ('Bob'), ('Charlie');");
    	
    	    // Set up the Run SQL button click handler
    	    document.getElementById('run-sql').addEventListener('click', () => {
    	        const sql = editor.getValue();
    	        try {
    	            const result = db.exec(sql);
    	            displayResults(result);
    	        } catch (err) {
    	            alert(`Error: ${err.message}`);
    	        }
    	    });
    	});
    	
    	// Function to display query results
    	function displayResults(result) {
    	    const resultsContainer = document.getElementById('results');
    	    resultsContainer.innerHTML = '';
    	    
    	    if (result.length === 0) {
    	        resultsContainer.innerText = 'No results.';
    	        return;
    	    }
    	
    	    result.forEach(({ columns, values }) => {
    	        const table = document.createElement('table');
    	        table.classList.add('result-table');
    	        
    	        const thead = document.createElement('thead');
    	        const trHead = document.createElement('tr');
    	        columns.forEach(column => {
    	            const th = document.createElement('th');
    	            th.innerText = column;
    	            trHead.appendChild(th);
    	        });
    	        thead.appendChild(trHead);
    	        table.appendChild(thead);
    	        
    	        const tbody = document.createElement('tbody');
    	        values.forEach(row => {
    	            const tr = document.createElement('tr');
    	            row.forEach(value => {
    	                const td = document.createElement('td');
    	                td.innerText = value;
    	                tr.appendChild(td);
    	            });
    	            tbody.appendChild(tr);
    	        });
    	        table.appendChild(tbody);
    	        
    	        resultsContainer.appendChild(table);
    	    });
    	}

    </script>
</body>
</html>
